﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;
using Microsoft.Win32;
namespace BanHangCshap.Class
{
    class SQLServer
    {
        public string strconn = "";
        public SqlConnection sqlconn = null;
        public SQLServer()
        {
            //if (DatabaseStatic.authenwindows == "true")
            //    strconn = "Data Source=" + DatabaseStatic.servername + ";Initial Catalog=" + DatabaseStatic.database + ";Integrated Security=True";
            //else
            //    strconn = "Data Source=" + DatabaseStatic.servername + ";Initial Catalog=" + DatabaseStatic.database + ";User ID=" + DatabaseStatic.username + ";Password=" + DatabaseStatic.password;
            //try
            //{
            //    sqlconn = new SqlConnection(strconn);
            //    sqlconn.Open();
            //}
            //catch (Exception)
            //{
            //    sqlconn = null;
            //}
            strconn = "Server = " + DatabaseStatic.servername + "; uid=sa; pwd=;Integrated Security = SSPI";
            try
            {
                sqlconn = new SqlConnection(strconn);
                sqlconn.Open();
            }
            catch (Exception)
            {
                sqlconn = null;
            }
        }
        public SQLServer(string strconn)
        {
            try
            {
                sqlconn = new SqlConnection(strconn);
                sqlconn.Open();
            }
            catch (Exception)
            {
                sqlconn = null;
            }
        }
        public bool executeSQLCommand(string query)
        {
            bool ok = false;
            SqlCommand cmd = new SqlCommand();
            cmd = new SqlCommand(query, sqlconn);
            try
            {
                cmd.ExecuteNonQuery();
                ok = true;
            }
            catch (Exception)
            {

                ok = false;
            }
            return ok;
        }
        public DataTable getAllDatabase()
        {

            DataTable dt = new DataTable();
            try
            {
                string sql = "SELECT [name] FROM [master].[dbo].[sysdatabases]";
                //SqlConnection sqlconn = new SqlConnection(strconn);
                //sqlconn.Open();
                SqlDataAdapter da = new SqlDataAdapter(sql, sqlconn);
                da.Fill(dt);
            }
            catch (Exception)
            {
                dt = null;

            }
            return dt;
        }
        public Boolean isExistDatabase(string databasename)
        {
            Boolean ok = false;
            DataTable dt = getAllDatabase();
            foreach (DataRow dr in dt.Rows)
            {
                if (dr[0].ToString().Trim().Equals(databasename))
                {
                    ok = true;
                    break;
                }
            }
            return ok;
        }
        public Boolean createDatabase(string databasename, string databasepath, string backuppath)
        {
            string sql = "Use Master ";
            sql += "Declare @DatabaseName		sysName ";
            sql += "Declare @PrimaryName		sysName ";
            sql += "Declare @LogName		sysName ";
            sql += "Declare @Path			sysName ";
            sql += "Declare @PrimaryFileName	sysName ";
            sql += "Declare @LogFileName		sysName ";
            sql += "Declare @BackUpFileName		nvarchar(4000) ";
            //sql += "Select 	@DatabaseName 		= '%Database%' , ";
            sql += "Select 	@DatabaseName 		= '" + databasename + "' , ";
            sql += "@PrimaryName		= @DatabaseName + '_Data' , ";
            sql += "@LogName			= @DatabaseName + '_Log'  , ";
            //sql += "@Path 				= '%DatabasePath%'			  , ";
            sql += "@Path 				= '" + databasepath + "'			  , ";
            sql += "@PrimaryFileName	= @Path + @PrimaryName + '.mdf' , ";
            sql += "@LogFileName		= @Path + @LogName + '.ldf' , ";
            //sql += "@BackUpFileName		= '%BackUpPath%' ";
            sql += "@BackUpFileName		= '" + backuppath + "' ";
            sql += "If Exists	(Select * ";
            sql += "From sysDatabases ";
            sql += "Where name = @DatabaseName ";
            sql += ") ";
            sql += "Execute ('Drop Database ' + @DatabaseName) ";
            sql += "Execute(' ";
            sql += "CREATE DATABASE ' + @DatabaseName + ' ";
            sql += "ON  ";
            sql += "( NAME =  ' + @PrimaryName + ', ";
            sql += "FILENAME = ''' + @PrimaryFileName + ''', ";
            sql += "SIZE = 10, ";
            sql += "MAXSIZE = 50, ";
            sql += "FILEGROWTH = 5 ) ";
            sql += "LOG ON ";
            sql += "( NAME = ' + @LogName + ', ";
            sql += "FILENAME = ''' + @LogFileName + ''', ";
            sql += "SIZE = 5MB, ";
            sql += "MAXSIZE = 25MB, ";
            sql += "FILEGROWTH = 5MB ) ";
            sql += "')";
            return executeSQLCommand(sql);
        }
        public Boolean bakupDatabse(string databasename, string pathbakup)
        {
            string strBak = "BACKUP DATABASE " + databasename + " TO DISK ='" + pathbakup + "'";
            return executeSQLCommand(strBak);
        }
        public Boolean restoreDatabse(string databasename, string databasepath, string backuppath)
        {
            string DataFile = "";
            string LogFile = "";
            string sql = "Declare @DatabaseName		sysName ";
            sql += "Declare @PrimaryName		sysName ";
            sql += "Declare @LogName		sysName ";
            sql += "Declare @Path			sysName ";
            sql += "Declare @PrimaryFileName	sysName ";
            sql += "Declare @LogFileName		sysName ";
            sql += "Declare @BackUpFileName		nvarchar(4000) ";

            sql += "Select 	@DatabaseName 		= '" + databasename + "' , ";
            sql += "@PrimaryName		= @DatabaseName + '_Data' , ";
            sql += "@LogName			= @DatabaseName + '_Log'  , ";
            sql += "@Path 				= '" + databasepath + "'	, ";
            sql += "@PrimaryFileName	= @Path + @PrimaryName + '.mdf' , ";
            sql += "@LogFileName		= @Path + @LogName + '.ldf' , ";
            sql += "@BackUpFileName		= '" + backuppath + "' ";



            sql += "Use " + databasename + " ";

            sql += "Declare @OldPrimaryFile		nvarchar(1000) ";
            sql += "Declare @OldLogFile		nvarchar(1000) ";

            sql += "Select @OldPrimaryFile = Name ";
            sql += "From sysFiles ";
            sql += "Where FileId = 1 ";

            sql += "Select @OldLogFile = Name ";
            sql += "From sysFiles ";
            sql += "Where FileId = 2 ";

            sql += "Use Master ";

            sql += "Declare @Id		Int ";
            sql += "Declare @cmd	Varchar(1000) ";

            sql += "Declare c Cursor For ";
            sql += "Select spid ";
            sql += "From master..sysprocesses ";
            sql += "Where hostname  <> '' ";

            sql += "Open c ";


            sql += "Fetch Next From c Into @Id ";

            sql += "While(@@FETCH_STATUS<>-1) ";
            sql += "Begin ";
            sql += "If(@Id <> @@SPID) ";
            sql += "Begin ";
            sql += "Select @cmd = 'Kill ' + Cast(@Id as Varchar) ";
            sql += "Execute(@cmd) ";
            sql += "End ";
            sql += "Fetch Next From c Into @Id ";
            sql += "End ";

            sql += "Close c ";
            sql += "Deallocate c ";

            sql += "Declare @RestoreCommand nVarchar(4000) ";

            sql += "Select @RestoreCommand =' ";
            sql += "RESTORE FILELISTONLY  ";
            sql += "FROM DISK = ''' + @BackUpFileName + '''' ";


            sql += "Execute(@RestoreCommand)";


            //SqlConnection connection = new SqlConnection("Server = " + DatabaseStatic.servername + "; uid=sa; pwd=;Integrated Security = SSPI");
            // SqlConnection connection = new SqlConnection(ConnectionString);
            SqlCommand cmd = sqlconn.CreateCommand();
            SqlDataReader reader;

            cmd.CommandText = sql;

            try
            {

                // connection.Open();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    if (reader.GetString(2) == "D")
                        DataFile = reader.GetString(0);
                    else
                        LogFile = reader.GetString(0);
                }

                reader.Close();

                (reader as IDisposable).Dispose();

            }
            catch (SqlException)
            {
                //MessageBox.Show(ex.ToString(), "SQL Server Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

                return false;
            }
            catch (Exception)
            {
                //MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

                return false;
            }
            finally
            {
                //if (connection.State == ConnectionState.Open)
                //    connection.Close();

                cmd.Dispose();

                //connection.Dispose();

            }
            //return true;

            string sql1 = "Use " + databasename + " ";
            sql1 += "Declare @DatabaseName		sysName ";
            sql1 += "Declare @PrimaryName		sysName ";
            sql1 += "Declare @LogName		sysName ";
            sql1 += "Declare @Path			sysName ";
            sql1 += "Declare @PrimaryFileName	sysName ";
            sql1 += "Declare @LogFileName		sysName ";
            sql1 += "Declare @BackUpFileName		nvarchar(4000) ";
            //sql += "Select 	@DatabaseName 		= '%Database%' , ";
            sql1 += "Select 	@DatabaseName 		= '" + databasename + "' , ";
            sql1 += "@PrimaryName		= @DatabaseName + '_Data' , ";
            sql1 += "@LogName			= @DatabaseName + '_Log'  , ";
            //sql += "@Path 				= '%DatabasePath%'		  , ";
            sql1 += "@Path 				= '" + databasepath + "'		  , ";
            //sql += "@BackUpFileName		= '%BackUpPath%' ";
            sql1 += "@BackUpFileName		= '" + backuppath + "' ";
            sql1 += "Select @PrimaryFileName = filename ";
            sql1 += "From sysFiles ";
            sql1 += "Where fileid = 1 ";
            sql1 += "Select @LogFileName = filename ";
            sql1 += "From sysFiles ";
            sql1 += "Where fileid = 2 ";
            sql1 += "Use Master ";
            sql1 += "Declare @Id		Int ";
            sql1 += "Declare @cmd	Varchar(1000) ";

            sql1 += "Declare c Cursor For ";
            sql1 += "Select spid ";
            sql1 += "From master..sysprocesses ";
            sql1 += "Where hostname  <> '' ";
            sql1 += "Open c ";
            sql1 += "Fetch Next From c Into @Id ";
            sql1 += "While(@@FETCH_STATUS<>-1) ";
            sql1 += "Begin ";
            sql1 += "If(@Id <> @@SPID) ";
            sql1 += "Begin ";
            sql1 += "Select @cmd = 'Kill ' + Cast(@Id as Varchar) ";
            sql1 += "Execute(@cmd) ";
            sql1 += "End ";
            sql1 += "Fetch Next From c Into @Id ";
            sql1 += "End ";
            sql1 += "Close c ";
            sql1 += "Deallocate c ";
            sql1 += "Declare @RestoreCommand nVarchar(4000) ";
            sql1 += "Select @RestoreCommand =' ";
            sql1 += "RESTORE DATABASE ' + @DatabaseName + ' ";
            sql1 += "FROM DISK = ''' + @BackUpFileName + ''' ";
            sql1 += " WITH  ";
            sql1 += "MOVE ''" + DataFile + "'' TO ''' + @PrimaryFileName + ''' , ";
            sql1 += " MOVE ''" + LogFile + "''  TO ''' + @LogFileName + ''', REPLACE' ";
            sql1 += "Execute(@RestoreCommand)";

            return executeSQLCommand(sql1);
        }
    }
}
